/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package uit.tkorg.coauthorgraph.dbaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.tkorg.coauthorgraph.dto.JournalDTO;

/**
 *
 * @author Huynh Ngoc Tin
 */
public class JournalMapper extends MapperDB {
    public JournalMapper() throws Exception {
        super();
    }

    public JournalMapper(Connection con) {
        super(con);
    }

    /**
     * check if the specified conference exist in the DB
     * @param paperDTO
     * @return
     * @throws Exception
     */
    public boolean isJournalExisted(JournalDTO journalDTO) throws Exception {
        boolean isExist = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT * FROM coauthorgraph.journal journal");
            sql.append(" WHERE journal.name = ? AND journal.year = ?");
            Connection con = this.getConnection();
            if (con != null) {
                PreparedStatement stmt = con.prepareStatement(sql.toString());
                stmt.setString(1, journalDTO.getJournalName());
                stmt.setInt(2, journalDTO.getYear());
                ResultSet rs = stmt.executeQuery();
                if ((rs != null) && (rs.next())) {
                    isExist = true;
                }
                stmt.close();
            }
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return isExist;
    }

    /**
     * get the journal ID from journalName and year
     * @param journalName
     * @return
     * @throws Exception
     */
    public int getJournalID(String journalName, int year) throws Exception {
        int journalID = -1;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT journalID FROM coauthorgraph.journal journal");
            sql.append(" WHERE journal.journalName = ? AND journal.year = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, journalName);
            stmt.setInt(2, year);
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                journalID = rs.getInt("journalID");
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return journalID;
    }

    /**
     * insert a new conference into the DB
     * @param authorDTO
     * @throws Exception
     */
    public int insertNewJournal(JournalDTO journalDTO) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO coauthorgraph.journal(journalName, organization, year) ");
            sql.append(" VALUES(?, ?, ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, journalDTO.getJournalName());
            stmt.setString(2, journalDTO.getOrganization());
            stmt.setInt(3, journalDTO.getYear());
            stmt.execute();

            // get the last inserted ID
            ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
            int insertJournalID = -1;
            if (rs != null && rs.next())
                insertJournalID = rs.getInt(1);

            stmt.close();
            return insertJournalID;
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
    }

    /**
     * getJournalNameListForAuthorList
     * @param authorIDList
     * @return
     * @throws Exception
     */
    public ArrayList getJournalNameListForAuthorList(ArrayList authorIDList) throws Exception {
        ArrayList jourNameList = new ArrayList();
        StringBuffer sqlStr1 = new StringBuffer();
        sqlStr1.append(" SELECT j.journalID, j.journalName");
        sqlStr1.append(" FROM coauthorgraph.journal j, coauthorgraph.paper p");
        sqlStr1.append(" WHERE p.journalID = j.journalID AND p.paperID IN ");
        sqlStr1.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
        for (int i=0; i<authorIDList.size(); i++) {
            String curAuthorID = (String) authorIDList.get(i);
            if (i == authorIDList.size()-1)
                sqlStr1.append(curAuthorID);
            else
                sqlStr1.append(curAuthorID + ",");
        }
        sqlStr1.append(" ))");
        sqlStr1.append(" GROUP BY j.journalName");
        Connection conn = getConnection();
        PreparedStatement stmt1 = conn.prepareStatement(sqlStr1.toString());
        ResultSet rs1 = stmt1.executeQuery();

        while (rs1 != null && rs1.next()) {
            String jourName = rs1.getString("journalName");
            jourNameList.add(jourName);
        }

        rs1.close();
        stmt1.close();
        return jourNameList;
    }
}
